Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OPEN Statement

The `OPEN` statement executes the query associated with a cursor. It allocates database resources to process the query and identifies the result set -- the rows that match the query conditions. The cursor is positioned before the first row in the result set. For more information, see "Querying Data with PL/SQL".

Syntax

open statement ::=

Description of the illustration open_statement.gif

Keyword and Parameter Description

cursor_name

An explicit cursor previously declared within the current scope and not currently open.

cursor_parameter_name

A variable declared as the formal parameter of a cursor. (For the syntax of `cursor_parameter_declaration`, see "Cursor Declaration".) A cursor parameter can appear in a query wherever a constant can appear.

Usage Notes

Generally, PL/SQL parses an explicit cursor only the first time it is opened and parses a SQL statement (creating an implicit cursor) only the first time the statement is executed. All the parsed SQL statements are cached. A SQL statement is reparsed only if it is aged out of the cache by a new SQL statement. Although you must close a cursor before you can reopen it, PL/SQL need not reparse the associated `SELECT` statement. If you close, then immediately reopen the cursor, a reparse is definitely not needed.

Rows in the result set are not retrieved when the `OPEN` statement is executed. The `FETCH` statement retrieves the rows. With a `FOR` `UPDATE` cursor, the rows are locked when the cursor is opened.

If formal parameters are declared, actual parameters must be passed to the cursor. The formal parameters of a cursor must be `IN` parameters; they cannot return values to actual parameters. The values of actual parameters are used when the cursor is opened. The datatypes of the formal and actual parameters must be compatible. The query can also reference PL/SQL variables declared within its scope.

Unless you want to accept default values, each formal parameter in the cursor declaration must have a corresponding actual parameter in the `OPEN` statement. Formal parameters declared with a default value do not need a corresponding actual parameter. They assume their default values when the `OPEN` statement is executed.

You can associate the actual parameters in an `OPEN` statement with the formal parameters in a cursor declaration using positional or named notation.

If a cursor is currently open, you cannot use its name in a cursor `FOR` loop.

Examples

For examples, see the following:

Example 6-10, "Fetching With a Cursor"
Example 6-13, "Fetching Bulk Data With a Cursor"
Example 13-1, "Declaring and Assigning Values to Variables"

Related Topics

"CLOSE Statement"
"Cursor Declaration"
"FETCH Statement"
"LOOP Statements"